package cn.icanci.loopstack.plugin.dalgen.util;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.MessageFormat;
import java.util.*;

import org.apache.commons.lang3.StringUtils;

import cn.hutool.core.collection.CollectionUtil;
import cn.icanci.loopstack.plugin.dalgen.config.GlobalConfig;
import cn.icanci.loopstack.plugin.dalgen.model.ClassInfoVO;
import cn.icanci.loopstack.plugin.dalgen.model.FieldInfoVO;

/**
 * 库解析工具类,解析表和字段
 *
 * @author icanci
 * @since 1.0 Created in 2022/05/13 18:13
 */
public class DataBaseUtil {

    private static Map<String, String> typeMapping = new HashMap<>();

    static {
        typeMapping.put("int", "Integer");
        typeMapping.put("char", "String");
        typeMapping.put("varchar", "String");
        typeMapping.put("datetime", "Date");
        typeMapping.put("timestamp", "Date");
        typeMapping.put("bit", "Integer");
        typeMapping.put("tinyint", "Integer");
        typeMapping.put("smallint", "Integer");
        typeMapping.put("year", "Date");
        typeMapping.put("date", "Date");
        typeMapping.put("bigint", "Long");
        typeMapping.put("decimal", "BigDecimal");
        typeMapping.put("double", "Double");
        typeMapping.put("float", "Float");
        typeMapping.put("numeric", "Integer");
        typeMapping.put("text", "String");
        typeMapping.put("mediumtext", "String");
        typeMapping.put("longtext", "String");
        typeMapping.put("time", "Date");
    }

    /***
     * 根据指定库获取单表相关参数
     *
     * @param tableName 表名
     * @param removePreTables 移除的前缀
     */
    public static ClassInfoVO parseClassInfo(String tableName, Set<String> removePreTables) throws SQLException {
        System.out.println("parseClassInfo from table: " + tableName);
        // tableSql
        String tableInfoSql = getTableInfoSql(tableName);

        Statement statement = DBUtil.getConnection().createStatement();

        ResultSet tableResult = statement.executeQuery(tableInfoSql);

        // 构建ClassInfo信息
        ClassInfoVO classInfo = new ClassInfoVO();
        classInfo.setTableName(tableName);
        String targetTableName = tableName;
        if (CollectionUtil.isNotEmpty(removePreTables)) {
            for (String removePreTable : removePreTables) {
                targetTableName = tableName.replace(removePreTable, "");
            }
        }

        // className信息
        String className = StringUtil.upperCaseFirst(StringUtil.underlineToCamelCase(tableName));
        classInfo.setClassName(className);
        classInfo.setRealClassName(StringUtil.upperCaseFirst(StringUtil.underlineToCamelCase(targetTableName)) + "DO");
        classInfo.setRealClassMapperName(StringUtil.upperCaseFirst(StringUtil.underlineToCamelCase(targetTableName)));
        classInfo.setRealClassVOName(StringUtil.upperCaseFirst(StringUtil.underlineToCamelCase(targetTableName)) + "VO");
        classInfo.setModelName(StringUtil.lowerCaseFirst(className));
        classInfo.setModelVOName(StringUtil.lowerCaseFirst(className) + "VO");

        classInfo.setClassComment(className);

        List<FieldInfoVO> fieldList = new ArrayList<>();

        // 1 column_name, 2 data_type 3 column_comment
        while (tableResult.next()) {
            FieldInfoVO fieldInfo = new FieldInfoVO();
            fieldInfo.setColumnName(tableResult.getString(1));
            fieldInfo.setFieldClass(typeMapping.get(tableResult.getString(2)));

            String fieldName = StringUtil.underlineToCamelCase(tableResult.getString(1));
            fieldInfo.setFieldName(fieldName);
            fieldInfo.setFieldNameGetSet(StringUtil.upperCaseFirst(fieldName));
            fieldInfo.setFieldComment(tableResult.getString(3));

            // 维护表结构字段 2 data_type,4 6 length, 7 nullAble
            fieldInfo.setDataType(tableResult.getString(2));
            fieldInfo.setMaxLength(StringUtils.isNotBlank(tableResult.getString(4)) ? tableResult.getString(4) : tableResult.getString(6));
            fieldInfo.setNullAble(tableResult.getString(7));
            fieldList.add(fieldInfo);
        }

        classInfo.setFieldList(fieldList);

        // 设置主键字段
        if (CollectionUtil.isEmpty(fieldList)) {
            classInfo.setKey(new FieldInfoVO());
        } else {
            classInfo.setKey(fieldList.get(0));
        }
        tableResult.close();
        statement.close();
        return classInfo;
    }

    /***
     * 根据指定库获取所有表名
     * 
     * @return 根据指定库获取所有表名
     */
    public static List<String> getAllTableNames() throws SQLException {
        // result
        List<String> result = new ArrayList<>();

        // sql
        String sql = getTables();

        Statement statement = DBUtil.getConnection().createStatement();

        ResultSet rs = statement.executeQuery(sql);

        while (rs.next()) {
            result.add(rs.getString(1));
        }
        return result;
    }

    /***
     * TableInfo SQL
     *
     * @param tableName tableName
     */
    private static String getTableInfoSql(String tableName) {
        return MessageFormat.format(
            "select column_name,data_type,column_comment,numeric_precision," + "numeric_scale,character_maximum_length,is_nullable nullable from information_schema.columns "
                                    + "where table_name = \"{0}\" and table_schema = \"{1}\"",
            tableName, GlobalConfig.getGobleConfig().getDataBase());
    }

    /***
     * 获取所有Tables 
     * 
     * @return Tables
     */
    private static String getTables() {
        return MessageFormat.format("select table_name from information_schema.tables where table_schema=\"{0}\" and table_type=\"{1}\";",
            GlobalConfig.getGobleConfig().getDataBase(), "base table");
    }

}
